{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# GitHub on Hacker News trends analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from __future__ import print_function\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Input parameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "project_id = \"your-project-id\"\n",
    "process_date = \"2018-12-01\"\n",
    "process_date_nodash = \"20181201\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploratory Data Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Github activity data\n",
    "- Link: [Data](https://bigquery.cloud.google.com/table/githubarchive:day.20181230) - [More info](https://blog.github.com/2017-01-19-github-data-ready-for-you-to-explore-with-bigquery/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Different event type in Gihub activity\n",
    "- [Event Types & Payloads](https://developer.github.com/v3/activity/events/types/) explaination"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT \n",
      "  type,\n",
      "  COUNT(*) AS cnt\n",
      "FROM `githubarchive.day.20181201` \n",
      "GROUP BY 1\n",
      "ORDER BY 2 DESC\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PushEvent</td>\n",
       "      <td>588724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CreateEvent</td>\n",
       "      <td>155010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WatchEvent</td>\n",
       "      <td>67607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PullRequestEvent</td>\n",
       "      <td>56635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>IssueCommentEvent</td>\n",
       "      <td>46972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>IssuesEvent</td>\n",
       "      <td>27592</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ForkEvent</td>\n",
       "      <td>24331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>DeleteEvent</td>\n",
       "      <td>22590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>PullRequestReviewCommentEvent</td>\n",
       "      <td>9756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>MemberEvent</td>\n",
       "      <td>5201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>GollumEvent</td>\n",
       "      <td>4445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ReleaseEvent</td>\n",
       "      <td>3527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>CommitCommentEvent</td>\n",
       "      <td>1759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>PublicEvent</td>\n",
       "      <td>1064</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             type     cnt\n",
       "0                       PushEvent  588724\n",
       "1                     CreateEvent  155010\n",
       "2                      WatchEvent   67607\n",
       "3                PullRequestEvent   56635\n",
       "4               IssueCommentEvent   46972\n",
       "5                     IssuesEvent   27592\n",
       "6                       ForkEvent   24331\n",
       "7                     DeleteEvent   22590\n",
       "8   PullRequestReviewCommentEvent    9756\n",
       "9                     MemberEvent    5201\n",
       "10                    GollumEvent    4445\n",
       "11                   ReleaseEvent    3527\n",
       "12             CommitCommentEvent    1759\n",
       "13                    PublicEvent    1064"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "  type,\n",
    "  COUNT(*) AS cnt\n",
    "FROM `githubarchive.day.{0}` \n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC\n",
    "\"\"\".format(process_date_nodash)\n",
    "\n",
    "print (query)\n",
    "\n",
    "df = pd.read_gbq(query, project_id=project_id, dialect='standard')\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 repos with the most comments in their issues\n",
    "- __IssueCommentEvent__: Triggered when an issue comment is created, edited, or deleted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT \n",
      "  repo.name,\n",
      "  COUNT(*) AS cnt\n",
      "FROM `githubarchive.day.20181201`\n",
      "WHERE type IN ( 'IssueCommentEvent')\n",
      "GROUP BY 1\n",
      "ORDER BY 2 DESC\n",
      "LIMIT 10\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>google-test/signcla-probe-repo</td>\n",
       "      <td>327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Azure/azure-rest-api-specs</td>\n",
       "      <td>287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>kubernetes/kubernetes</td>\n",
       "      <td>227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>rust-lang/rust</td>\n",
       "      <td>207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>apache/spark</td>\n",
       "      <td>204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>freeCodeCamp/freeCodeCamp</td>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>everypolitician/everypolitician-data</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>TeamNewPipe/NewPipe</td>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>openshift/origin</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NixOS/nixpkgs</td>\n",
       "      <td>126</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   name  cnt\n",
       "0        google-test/signcla-probe-repo  327\n",
       "1            Azure/azure-rest-api-specs  287\n",
       "2                 kubernetes/kubernetes  227\n",
       "3                        rust-lang/rust  207\n",
       "4                          apache/spark  204\n",
       "5             freeCodeCamp/freeCodeCamp  196\n",
       "6  everypolitician/everypolitician-data  192\n",
       "7                   TeamNewPipe/NewPipe  158\n",
       "8                      openshift/origin  140\n",
       "9                         NixOS/nixpkgs  126"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "  repo.name,\n",
    "  COUNT(*) AS cnt\n",
    "FROM `githubarchive.day.{0}`\n",
    "WHERE type IN ( 'IssueCommentEvent')\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC\n",
    "LIMIT 10\n",
    "\"\"\".format(process_date_nodash)\n",
    "\n",
    "print (query)\n",
    "\n",
    "df = pd.read_gbq(query, project_id=project_id, dialect='standard')\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 repos by stars and fork event"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT \n",
      "  repo.name,\n",
      "  SUM(IF(type='WatchEvent', 1, NULL)) AS stars,\n",
      "  SUM(IF(type='ForkEvent',  1, NULL)) AS forks,\n",
      "  COUNT(*) AS cnt\n",
      "FROM `githubarchive.day.20181201`\n",
      "WHERE type IN ('WatchEvent','ForkEvent')\n",
      "GROUP BY 1\n",
      "ORDER BY 2 DESC\n",
      "LIMIT 10\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>stars</th>\n",
       "      <th>forks</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BcRikko/NES.css</td>\n",
       "      <td>386</td>\n",
       "      <td>35</td>\n",
       "      <td>421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>leisurelicht/wtfpython-cn</td>\n",
       "      <td>241</td>\n",
       "      <td>31</td>\n",
       "      <td>272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>satwikkansal/wtfpython</td>\n",
       "      <td>190</td>\n",
       "      <td>30</td>\n",
       "      <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cssanimation/css-animation-101</td>\n",
       "      <td>178</td>\n",
       "      <td>5</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>firecracker-microvm/firecracker</td>\n",
       "      <td>150</td>\n",
       "      <td>13</td>\n",
       "      <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>crazyandcoder/kindle_free_books</td>\n",
       "      <td>132</td>\n",
       "      <td>31</td>\n",
       "      <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>withspectrum/spectrum</td>\n",
       "      <td>132</td>\n",
       "      <td>9</td>\n",
       "      <td>141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>afshinea/stanford-cs-230-deep-learning</td>\n",
       "      <td>120</td>\n",
       "      <td>17</td>\n",
       "      <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>algorithm-visualizer/algorithm-visualizer</td>\n",
       "      <td>119</td>\n",
       "      <td>15</td>\n",
       "      <td>134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>olifolkerd/tabulator</td>\n",
       "      <td>114</td>\n",
       "      <td>3</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        name  stars  forks  cnt\n",
       "0                            BcRikko/NES.css    386     35  421\n",
       "1                  leisurelicht/wtfpython-cn    241     31  272\n",
       "2                     satwikkansal/wtfpython    190     30  220\n",
       "3             cssanimation/css-animation-101    178      5  183\n",
       "4            firecracker-microvm/firecracker    150     13  163\n",
       "5            crazyandcoder/kindle_free_books    132     31  163\n",
       "6                      withspectrum/spectrum    132      9  141\n",
       "7     afshinea/stanford-cs-230-deep-learning    120     17  137\n",
       "8  algorithm-visualizer/algorithm-visualizer    119     15  134\n",
       "9                       olifolkerd/tabulator    114      3  117"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "  repo.name,\n",
    "  SUM(IF(type='WatchEvent', 1, NULL)) AS stars,\n",
    "  SUM(IF(type='ForkEvent',  1, NULL)) AS forks,\n",
    "  COUNT(*) AS cnt\n",
    "FROM `githubarchive.day.{0}`\n",
    "WHERE type IN ('WatchEvent','ForkEvent')\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC\n",
    "LIMIT 10\n",
    "\"\"\".format(process_date_nodash)\n",
    "\n",
    "print (query)\n",
    "\n",
    "df = pd.read_gbq(query, project_id=project_id, dialect='standard')\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Hacker News data\n",
    "- Link: [Data](https://bigquery.cloud.google.com/table/bigquery-public-data:hacker_news.full) - [More info](https://medium.com/@hoffa/hacker-news-on-bigquery-now-with-daily-updates-so-what-are-the-top-domains-963d3c68b2e2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top domains shared in Hacker News\n",
    "- Domain with higher score are more likely to make it to the front page.\n",
    "- __nytimes__ has the highest average score."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT \n",
      "  REGEXP_EXTRACT(url, '//([^/]*)/?') as domain,\n",
      "  AVG(score) as avg_score,\n",
      "  COUNT(*) AS cnt\n",
      "FROM `bigquery-public-data.hacker_news.full`\n",
      "WHERE url!='' \n",
      "AND EXTRACT(DATE FROM timestamp)=\"2018-12-01\"\n",
      "GROUP BY 1\n",
      "ORDER BY 3 DESC \n",
      "LIMIT 10\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>domain</th>\n",
       "      <th>avg_score</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>github.com</td>\n",
       "      <td>14.966667</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>medium.com</td>\n",
       "      <td>15.592593</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>www.youtube.com</td>\n",
       "      <td>12.666667</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>www.nytimes.com</td>\n",
       "      <td>41.263158</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>venturebeat.com</td>\n",
       "      <td>2.100000</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>www.reddit.com</td>\n",
       "      <td>21.428571</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>www.theguardian.com</td>\n",
       "      <td>31.166667</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>en.wikipedia.org</td>\n",
       "      <td>15.833333</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>arstechnica.com</td>\n",
       "      <td>22.666667</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>www.theverge.com</td>\n",
       "      <td>2.200000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                domain  avg_score  cnt\n",
       "0           github.com  14.966667   30\n",
       "1           medium.com  15.592593   27\n",
       "2      www.youtube.com  12.666667   24\n",
       "3      www.nytimes.com  41.263158   19\n",
       "4      venturebeat.com   2.100000   10\n",
       "5       www.reddit.com  21.428571    7\n",
       "6  www.theguardian.com  31.166667    6\n",
       "7     en.wikipedia.org  15.833333    6\n",
       "8      arstechnica.com  22.666667    6\n",
       "9     www.theverge.com   2.200000    5"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "  REGEXP_EXTRACT(url, '//([^/]*)/?') as domain,\n",
    "  AVG(score) as avg_score,\n",
    "  COUNT(*) AS cnt\n",
    "FROM `bigquery-public-data.hacker_news.full`\n",
    "WHERE url!='' \n",
    "AND EXTRACT(DATE FROM timestamp)=\"{0}\"\n",
    "GROUP BY 1\n",
    "ORDER BY 3 DESC \n",
    "LIMIT 10\n",
    "\"\"\".format(process_date)\n",
    "\n",
    "print (query)\n",
    "\n",
    "df = pd.read_gbq(query, project_id=project_id, dialect='standard')\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What domains have the best chance of getting more than 40 upvotes?\n",
    "- Certainly Hacker News likes content hosted on sites like github.com and the nytimes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT \n",
      "  REGEXP_EXTRACT(url, '//([^/]*)/?') as domain,\n",
      "  COUNTIF(score>40) as score_gt_40,\n",
      "  COUNT(*) AS cnt\n",
      "FROM `bigquery-public-data.hacker_news.full`\n",
      "WHERE url!='' \n",
      "AND EXTRACT(DATE FROM timestamp)=\"2018-12-01\"\n",
      "GROUP BY 1\n",
      "ORDER BY 2 DESC \n",
      "LIMIT 10\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>domain</th>\n",
       "      <th>score_gt_40</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>www.nytimes.com</td>\n",
       "      <td>4</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>github.com</td>\n",
       "      <td>4</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>medium.com</td>\n",
       "      <td>3</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>www.wsj.com</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>www.theatlantic.com</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>www.youtube.com</td>\n",
       "      <td>2</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>www.jamiefuller.com</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>arstechnica.com</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>www.vulture.com</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>www.newsshooter.com</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                domain  score_gt_40  cnt\n",
       "0      www.nytimes.com            4   19\n",
       "1           github.com            4   30\n",
       "2           medium.com            3   27\n",
       "3          www.wsj.com            2    4\n",
       "4  www.theatlantic.com            2    5\n",
       "5      www.youtube.com            2   24\n",
       "6  www.jamiefuller.com            1    1\n",
       "7      arstechnica.com            1    6\n",
       "8      www.vulture.com            1    2\n",
       "9  www.newsshooter.com            1    1"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "  REGEXP_EXTRACT(url, '//([^/]*)/?') as domain,\n",
    "  COUNTIF(score>40) as score_gt_40,\n",
    "  COUNT(*) AS cnt\n",
    "FROM `bigquery-public-data.hacker_news.full`\n",
    "WHERE url!='' \n",
    "AND EXTRACT(DATE FROM timestamp)=\"{0}\"\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC \n",
    "LIMIT 10\n",
    "\"\"\".format(process_date)\n",
    "\n",
    "print (query)\n",
    "\n",
    "df = pd.read_gbq(query, project_id=project_id, dialect='standard')\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 Hacker news stories from Github by highest score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT     \n",
      "  `by` AS submitter,\n",
      "  id as story_id,\n",
      "  REGEXP_EXTRACT(url, \"(https?://github.com/[^/]*/[^/#?]*)\") as url,\n",
      "  SUM(score) as score\n",
      "FROM\n",
      "  `bigquery-public-data.hacker_news.full`\n",
      "WHERE\n",
      "  type = 'story'\n",
      "  AND EXTRACT(DATE FROM timestamp)='2018-12-01' \n",
      "  AND url LIKE '%https://github.com%'\n",
      "  AND url NOT LIKE '%github.com/blog/%'\n",
      "GROUP BY  \n",
      "  submitter,\n",
      "  story_id,\n",
      "  url\n",
      "ORDER BY score DESC\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>submitter</th>\n",
       "      <th>story_id</th>\n",
       "      <th>url</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ithinco</td>\n",
       "      <td>18574181</td>\n",
       "      <td>https://github.com/ithinco/i-am-chinese-the-dr...</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mountainview</td>\n",
       "      <td>18576170</td>\n",
       "      <td>https://github.com/YugaByte/yugabyte-db</td>\n",
       "      <td>115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>oxplot</td>\n",
       "      <td>18575094</td>\n",
       "      <td>https://github.com/oxplot/pdftilecut</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>codeadict</td>\n",
       "      <td>18574683</td>\n",
       "      <td>https://github.com/alertlogic/erllambda</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>pjmlp</td>\n",
       "      <td>18575802</td>\n",
       "      <td>https://github.com/chocolatey/boxstarter</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>snek</td>\n",
       "      <td>18577658</td>\n",
       "      <td>https://github.com/devsnek/engine262</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>delvincasper</td>\n",
       "      <td>18577036</td>\n",
       "      <td>https://github.com/jerverless/jerverless</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>andrewchaa</td>\n",
       "      <td>18574107</td>\n",
       "      <td>https://github.com/andrewchaa/functional.pipe</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>anmonteiro90</td>\n",
       "      <td>18578964</td>\n",
       "      <td>https://github.com/anmonteiro/aws-lambda-ocaml...</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>KumarAbhirup</td>\n",
       "      <td>18577887</td>\n",
       "      <td>https://github.com/KumarAbhirup/bulk-mail-cli</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      submitter  story_id                                                url  \\\n",
       "0       ithinco  18574181  https://github.com/ithinco/i-am-chinese-the-dr...   \n",
       "1  mountainview  18576170            https://github.com/YugaByte/yugabyte-db   \n",
       "2        oxplot  18575094               https://github.com/oxplot/pdftilecut   \n",
       "3     codeadict  18574683            https://github.com/alertlogic/erllambda   \n",
       "4         pjmlp  18575802           https://github.com/chocolatey/boxstarter   \n",
       "5          snek  18577658               https://github.com/devsnek/engine262   \n",
       "6  delvincasper  18577036           https://github.com/jerverless/jerverless   \n",
       "7    andrewchaa  18574107      https://github.com/andrewchaa/functional.pipe   \n",
       "8  anmonteiro90  18578964  https://github.com/anmonteiro/aws-lambda-ocaml...   \n",
       "9  KumarAbhirup  18577887      https://github.com/KumarAbhirup/bulk-mail-cli   \n",
       "\n",
       "   score  \n",
       "0    129  \n",
       "1    115  \n",
       "2     64  \n",
       "3     64  \n",
       "4      9  \n",
       "5      8  \n",
       "6      4  \n",
       "7      4  \n",
       "8      4  \n",
       "9      4  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT     \n",
    "  `by` AS submitter,\n",
    "  id as story_id,\n",
    "  REGEXP_EXTRACT(url, \"(https?://github.com/[^/]*/[^/#?]*)\") as url,\n",
    "  SUM(score) as score\n",
    "FROM\n",
    "  `bigquery-public-data.hacker_news.full`\n",
    "WHERE\n",
    "  type = 'story'\n",
    "  AND EXTRACT(DATE FROM timestamp)='{0}' \n",
    "  AND url LIKE '%https://github.com%'\n",
    "  AND url NOT LIKE '%github.com/blog/%'\n",
    "GROUP BY  \n",
    "  submitter,\n",
    "  story_id,\n",
    "  url\n",
    "ORDER BY score DESC\n",
    "\"\"\".format(process_date)\n",
    "\n",
    "print (query)\n",
    "\n",
    "df = pd.read_gbq(query, project_id=project_id, dialect='standard')\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example Final table: GitHub on Hacker News Trends of 2018-12-01"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "WITH github_activity AS (\n",
      "SELECT   \n",
      "  repo.name as repo,\n",
      "  CONCAT('https://github.com/', repo.name) as url,\n",
      "  SUM(IF(type='WatchEvent', 1, NULL)) AS stars,\n",
      "  SUM(IF(type='ForkEvent',  1, NULL)) AS forks,\n",
      "  COUNT(*) AS cnt\n",
      "FROM `githubarchive.day.20181201`\n",
      "WHERE type IN ('WatchEvent','ForkEvent')\n",
      "GROUP BY 1,2\n",
      "),\n",
      "hacker_news AS (\n",
      "SELECT\n",
      "  EXTRACT(DATE FROM timestamp) as date,\n",
      "  `by` AS submitter,\n",
      "  id as story_id,\n",
      "  REGEXP_EXTRACT(url, \"(https?://github.com/[^/]*/[^/#?]*)\") as url,\n",
      "  SUM(score) as score\n",
      "FROM\n",
      "  `bigquery-public-data.hacker_news.full`\n",
      "WHERE\n",
      "  type = 'story'\n",
      "  AND EXTRACT(DATE FROM timestamp)='2018-12-01' \n",
      "  AND url LIKE '%https://github.com%'\n",
      "  AND url NOT LIKE '%github.com/blog/%'\n",
      "GROUP BY 1,2,3,4\n",
      ")\n",
      "\n",
      "SELECT\n",
      "  a.date as date,\n",
      "  a.url as github_url,\n",
      "  b.repo as github_repo,\n",
      "  a.score as hn_score,\n",
      "  a.story_id as hn_story_id,\n",
      "  b.stars as stars,\n",
      "  b.forks as forks\n",
      "FROM hacker_news as a\n",
      "LEFT JOIN github_activity as b\n",
      "ON a.url=b.url\n",
      "ORDER BY hn_score DESC\n",
      "LIMIT 10\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>github_url</th>\n",
       "      <th>github_repo</th>\n",
       "      <th>hn_score</th>\n",
       "      <th>hn_story_id</th>\n",
       "      <th>stars</th>\n",
       "      <th>forks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>https://github.com/ithinco/i-am-chinese-the-dr...</td>\n",
       "      <td>ithinco/i-am-chinese-the-dragonfly-must-go-on</td>\n",
       "      <td>129</td>\n",
       "      <td>18574181</td>\n",
       "      <td>60.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>https://github.com/YugaByte/yugabyte-db</td>\n",
       "      <td>YugaByte/yugabyte-db</td>\n",
       "      <td>115</td>\n",
       "      <td>18576170</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>https://github.com/alertlogic/erllambda</td>\n",
       "      <td>alertlogic/erllambda</td>\n",
       "      <td>64</td>\n",
       "      <td>18574683</td>\n",
       "      <td>48.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>https://github.com/oxplot/pdftilecut</td>\n",
       "      <td>oxplot/pdftilecut</td>\n",
       "      <td>64</td>\n",
       "      <td>18575094</td>\n",
       "      <td>91.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>https://github.com/chocolatey/boxstarter</td>\n",
       "      <td>chocolatey/boxstarter</td>\n",
       "      <td>9</td>\n",
       "      <td>18575802</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>https://github.com/devsnek/engine262</td>\n",
       "      <td>devsnek/engine262</td>\n",
       "      <td>8</td>\n",
       "      <td>18577658</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>https://github.com/andrewchaa/functional.pipe</td>\n",
       "      <td>andrewchaa/functional.pipe</td>\n",
       "      <td>4</td>\n",
       "      <td>18574107</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>https://github.com/anmonteiro/aws-lambda-ocaml...</td>\n",
       "      <td>anmonteiro/aws-lambda-ocaml-runtime</td>\n",
       "      <td>4</td>\n",
       "      <td>18578964</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>https://github.com/KumarAbhirup/bulk-mail-cli</td>\n",
       "      <td>None</td>\n",
       "      <td>4</td>\n",
       "      <td>18577887</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>https://github.com/jerverless/jerverless</td>\n",
       "      <td>None</td>\n",
       "      <td>4</td>\n",
       "      <td>18577036</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date                                         github_url  \\\n",
       "0 2018-12-01  https://github.com/ithinco/i-am-chinese-the-dr...   \n",
       "1 2018-12-01            https://github.com/YugaByte/yugabyte-db   \n",
       "2 2018-12-01            https://github.com/alertlogic/erllambda   \n",
       "3 2018-12-01               https://github.com/oxplot/pdftilecut   \n",
       "4 2018-12-01           https://github.com/chocolatey/boxstarter   \n",
       "5 2018-12-01               https://github.com/devsnek/engine262   \n",
       "6 2018-12-01      https://github.com/andrewchaa/functional.pipe   \n",
       "7 2018-12-01  https://github.com/anmonteiro/aws-lambda-ocaml...   \n",
       "8 2018-12-01      https://github.com/KumarAbhirup/bulk-mail-cli   \n",
       "9 2018-12-01           https://github.com/jerverless/jerverless   \n",
       "\n",
       "                                     github_repo  hn_score  hn_story_id  \\\n",
       "0  ithinco/i-am-chinese-the-dragonfly-must-go-on       129     18574181   \n",
       "1                           YugaByte/yugabyte-db       115     18576170   \n",
       "2                           alertlogic/erllambda        64     18574683   \n",
       "3                              oxplot/pdftilecut        64     18575094   \n",
       "4                          chocolatey/boxstarter         9     18575802   \n",
       "5                              devsnek/engine262         8     18577658   \n",
       "6                     andrewchaa/functional.pipe         4     18574107   \n",
       "7            anmonteiro/aws-lambda-ocaml-runtime         4     18578964   \n",
       "8                                           None         4     18577887   \n",
       "9                                           None         4     18577036   \n",
       "\n",
       "   stars  forks  \n",
       "0   60.0    1.0  \n",
       "1    2.0    NaN  \n",
       "2   48.0    NaN  \n",
       "3   91.0    NaN  \n",
       "4    1.0    NaN  \n",
       "5    1.0    NaN  \n",
       "6    2.0    NaN  \n",
       "7    5.0    NaN  \n",
       "8    NaN    NaN  \n",
       "9    NaN    NaN  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "WITH github_activity AS (\n",
    "SELECT   \n",
    "  repo.name as repo,\n",
    "  CONCAT('https://github.com/', repo.name) as url,\n",
    "  SUM(IF(type='WatchEvent', 1, NULL)) AS stars,\n",
    "  SUM(IF(type='ForkEvent',  1, NULL)) AS forks,\n",
    "  COUNT(*) AS cnt\n",
    "FROM `githubarchive.day.{0}`\n",
    "WHERE type IN ('WatchEvent','ForkEvent')\n",
    "GROUP BY 1,2\n",
    "),\n",
    "hacker_news AS (\n",
    "SELECT\n",
    "  EXTRACT(DATE FROM timestamp) as date,\n",
    "  `by` AS submitter,\n",
    "  id as story_id,\n",
    "  REGEXP_EXTRACT(url, \"(https?://github.com/[^/]*/[^/#?]*)\") as url,\n",
    "  SUM(score) as score\n",
    "FROM\n",
    "  `bigquery-public-data.hacker_news.full`\n",
    "WHERE\n",
    "  type = 'story'\n",
    "  AND EXTRACT(DATE FROM timestamp)='{1}' \n",
    "  AND url LIKE '%https://github.com%'\n",
    "  AND url NOT LIKE '%github.com/blog/%'\n",
    "GROUP BY 1,2,3,4\n",
    ")\n",
    "\n",
    "SELECT\n",
    "  a.date as date,\n",
    "  a.url as github_url,\n",
    "  b.repo as github_repo,\n",
    "  a.score as hn_score,\n",
    "  a.story_id as hn_story_id,\n",
    "  b.stars as stars,\n",
    "  b.forks as forks\n",
    "FROM hacker_news as a\n",
    "LEFT JOIN github_activity as b\n",
    "ON a.url=b.url\n",
    "ORDER BY hn_score DESC\n",
    "LIMIT 10\n",
    "\"\"\".format(process_date_nodash, process_date)\n",
    "\n",
    "print (query)\n",
    "\n",
    "df = pd.read_gbq(query, project_id=project_id, dialect='standard')\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Python PyPI stats\n",
    "- The Python Software Foundation provides the raw logs of Python installation activitie\n",
    "- Link: [Data](https://bigquery.cloud.google.com/table/the-psf:pypi.downloads20181230) - [More info](https://packaging.python.org/guides/analyzing-pypi-package-downloads/)\n",
    "\n",
    "__Challenge__: \n",
    "- Find associated Github stars, fork event, and Hacker News story for top downloads Python packages from pip"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Top 10 downloads packages from pip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT \n",
      "  TIMESTAMP_TRUNC(timestamp, DAY) as day,\n",
      "  file.project as project,\n",
      "  COUNT(*) as downloads\n",
      "FROM `the-psf.pypi.downloads20181201`\n",
      "WHERE details.installer.name = 'pip'\n",
      "GROUP BY 1,2\n",
      "ORDER BY 3 desc\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>day</th>\n",
       "      <th>project</th>\n",
       "      <th>downloads</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>pip</td>\n",
       "      <td>1562226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>urllib3</td>\n",
       "      <td>1271997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>botocore</td>\n",
       "      <td>1069194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>six</td>\n",
       "      <td>966172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>python-dateutil</td>\n",
       "      <td>946327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>s3transfer</td>\n",
       "      <td>877832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>docutils</td>\n",
       "      <td>813135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>pyyaml</td>\n",
       "      <td>796706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>pyasn1</td>\n",
       "      <td>782540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2018-12-01</td>\n",
       "      <td>jmespath</td>\n",
       "      <td>772065</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         day          project  downloads\n",
       "0 2018-12-01              pip    1562226\n",
       "1 2018-12-01          urllib3    1271997\n",
       "2 2018-12-01         botocore    1069194\n",
       "3 2018-12-01              six     966172\n",
       "4 2018-12-01  python-dateutil     946327\n",
       "5 2018-12-01       s3transfer     877832\n",
       "6 2018-12-01         docutils     813135\n",
       "7 2018-12-01           pyyaml     796706\n",
       "8 2018-12-01           pyasn1     782540\n",
       "9 2018-12-01         jmespath     772065"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "  TIMESTAMP_TRUNC(timestamp, DAY) as day,\n",
    "  file.project as project,\n",
    "  COUNT(*) as downloads\n",
    "FROM `the-psf.pypi.downloads{0}`\n",
    "WHERE details.installer.name = 'pip'\n",
    "GROUP BY 1,2\n",
    "ORDER BY 3 desc\n",
    "\"\"\".format(process_date_nodash)\n",
    "\n",
    "print (query)\n",
    "\n",
    "df = pd.read_gbq(query, project_id=project_id, dialect='standard')\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What is the number stars and fork event for botocore?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "SELECT \n",
      "  repo.name,\n",
      "  SUM(IF(type='WatchEvent', 1, NULL)) AS stars,\n",
      "  SUM(IF(type='ForkEvent',  1, NULL)) AS forks,\n",
      "  COUNT(*) AS cnt\n",
      "FROM `githubarchive.day.20181201`\n",
      "WHERE type IN ('WatchEvent','ForkEvent')\n",
      "AND repo.name LIKE \"%botocore%\"\n",
      "GROUP BY 1\n",
      "ORDER BY 2 DESC\n",
      "LIMIT 10\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>stars</th>\n",
       "      <th>forks</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>boto/botocore</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name  stars  forks  cnt\n",
       "0  boto/botocore      2      1    3"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT \n",
    "  repo.name,\n",
    "  SUM(IF(type='WatchEvent', 1, NULL)) AS stars,\n",
    "  SUM(IF(type='ForkEvent',  1, NULL)) AS forks,\n",
    "  COUNT(*) AS cnt\n",
    "FROM `githubarchive.day.{0}`\n",
    "WHERE type IN ('WatchEvent','ForkEvent')\n",
    "AND repo.name LIKE \"%botocore%\"\n",
    "GROUP BY 1\n",
    "ORDER BY 2 DESC\n",
    "LIMIT 10\n",
    "\"\"\".format(process_date_nodash)\n",
    "\n",
    "print (query)\n",
    "\n",
    "df = pd.read_gbq(query, project_id=project_id, dialect='standard')\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Resources\n",
    "- [GitHub data, ready for you to explore with BigQuery](https://blog.github.com/2017-01-19-github-data-ready-for-you-to-explore-with-bigquery/)\n",
    "- [Hacker News on BigQuery](https://medium.com/@hoffa/hacker-news-on-bigquery-now-with-daily-updates-so-what-are-the-top-domains-963d3c68b2e2)\n",
    "- [Analyzing PyPI package downloads](https://packaging.python.org/guides/analyzing-pypi-package-downloads/)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
